package edu.cmu.ibill.model;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.BeanFactory;
import org.mybeans.factory.BeanFactoryException;
import org.mybeans.factory.BeanTable;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;
import org.mybeans.factory.Transaction;

import java.util.Date;

import edu.cmu.ibill.databeans.PersonalBill;

/**
 * 
 * @author Yunkai Zhang
 * 
 */
public class PersonalBillDAO {

	SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");

	private BeanFactory<PersonalBill> factory;

	/**
	 * Constructor
	 * 
	 * @param driver
	 * @param URL
	 * @param user
	 * @param pw
	 * @throws DAOException
	 */
	public PersonalBillDAO(String driver, String URL, String user, String pw)
			throws DAOException {
		try {
			// Get a BeanTable to create the "photo" table

			BeanTable.useJDBC(driver, URL, user, pw);

			BeanTable<PersonalBill> personalBillTable = BeanTable.getInstance(
					PersonalBill.class, "ibill_personal");

			if (!personalBillTable.exists()) {
				personalBillTable.create("id");
			}

			personalBillTable.setIdleConnectionCleanup(true);

			// Get a BeanFactory which the actions will use to read and write
			// rows of the "likeable" table
			factory = personalBillTable.getFactory();
		} catch (BeanFactoryException e) {
			throw new DAOException(e);
		}
	}

	/**
	 * Lookup the PersonalBill using the personal bill id.
	 * 
	 * @param id
	 * @return
	 */
	public PersonalBill Lookup(int id) {
		PersonalBill bill = null;

		try {
			PersonalBill[] list = factory.match(MatchArg.equals("id", id));
			if (list.length > 0)
				bill = list[0];
		} catch (RollbackException e) {
			return null;
		}

		return bill;
	}

	/**
	 * Return all the personal bills created by a certain user
	 * 
	 * @param userid
	 * @return PersonalBill[]
	 */
	public PersonalBill[] LookupByUserId(int uid) {
		PersonalBill[] bills = null;

		try {
			bills = factory.match(MatchArg.equals("userid", uid));
		} catch (RollbackException e) {
			return null;
		}

		return bills;
	}

	/**
	 * Update the corresponding bill in the database
	 * 
	 * @param bill
	 * @throws DAOException
	 */
	public void update(PersonalBill bill) throws DAOException {
		try {
			Transaction.begin();

			PersonalBill dbBill = factory.lookup(bill.getId());

			if (dbBill == null)
				throw new DAOException("Cannot find the bill: " + bill.getId());

			factory.copyInto(bill, dbBill);

			Transaction.commit();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}

	/**
	 * Create a PersonalBill and insert it into the database.
	 * 
	 * @param bill
	 * @throws DAOException
	 */
	public int create(PersonalBill bill) throws DAOException {
		try {
			Transaction.begin();
			PersonalBill dbBill = factory.create();
			factory.copyInto(bill, dbBill);

			Transaction.commit();
			return dbBill.getId();
		} catch (RollbackException e) {
			throw new DAOException(e);
		} finally {
			if (Transaction.isActive())
				Transaction.rollback();
		}
	}

	/**
	 * Delete the PersonalBill from database
	 * 
	 * @param bill
	 * @throws DAOException
	 */
	public void delete(int id) throws DAOException {
		try {
			factory.delete(id);
		} catch (RollbackException e) {
			throw new DAOException(e);
		}
	}

	public double getCurrentMonthExpense(int uid) {

		Date date = new Date();
		date.setDate(1);
		String firstday = format.format(date);

		try {
			PersonalBill[] list = factory.match(MatchArg
					.greaterThanOrEqualTo("date", new java.sql.Date(format
							.parse(firstday).getTime())), MatchArg.equals(
					"userid", uid));
			if (list != null && list.length > 0) {
				double sum = 0;
				for (PersonalBill bill : list) {
					if (bill.getAmount() > 0)
						sum += bill.getAmount();
				}
				return sum;

			} else
				return 0;
		} catch (RollbackException e) {
			return 0;
		} catch (ParseException e) {
			return 0;
		}
	}

	public double getCurrentMonthIncome(int uid) {

		Date date = new Date();
		date.setDate(1);
		String firstday = format.format(date);

		try {
			PersonalBill[] list = factory.match(MatchArg
					.greaterThanOrEqualTo("date", new java.sql.Date(format
							.parse(firstday).getTime())), MatchArg.equals(
					"userid", uid));
			if (list != null && list.length > 0) {
				double sum = 0;
				for (PersonalBill bill : list) {
					if (bill.getAmount() < 0)
						sum += bill.getAmount();
				}
				return Math.abs(sum);

			} else
				return 0;
		} catch (RollbackException e) {
			return 0;
		} catch (ParseException e) {
			return 0;
		}
	}

	public double getMonthIncome(int year, int month, int uid) {
		Date Date = new Date();
		Date.setDate(1);
		Date.setMonth(month-1);
		Date.setYear(year);
		String startDay = format.format(Date);

		Date.setDate(1);
		if (month < 12) {
			Date.setMonth(month);
			Date.setYear(year);
		}
		else{
			Date.setMonth(month%12);
			Date.setYear(1+year);
		}
		String endDay = format.format(Date);

		try {
			PersonalBill[] list = factory.match(
					MatchArg.greaterThanOrEqualTo("date", new java.sql.Date(format.parse(startDay).getTime())), 
					MatchArg.lessThan("date", new java.sql.Date(format.parse(endDay).getTime())),
					MatchArg.equals("userid", uid));
			if (list != null && list.length > 0) {
				double sum = 0;
				for (PersonalBill bill : list) {
					if (bill.getAmount() < 0)
						sum += bill.getAmount();
				}
				return Math.abs(sum);

			} else
				return 0;
		} catch (RollbackException e) {
			return 0;
		} catch (ParseException e) {
			return 0;
		}
	}

	public double getMonthExpense(int year, int month, int uid) {

		Date Date = new Date();
		Date.setDate(1);
		Date.setMonth(month-1);
		Date.setYear(year);
		String startDay = format.format(Date);

		Date.setDate(1);
		if (month < 11) {
			Date.setMonth(month);
			Date.setYear(year);
		}
		else{
			Date.setMonth(month%12);
			Date.setYear(1+year);
		}
		String endDay = format.format(Date);

		try {
			PersonalBill[] list = factory.match(
					MatchArg.greaterThanOrEqualTo("date", new java.sql.Date(format.parse(startDay).getTime())), 
					MatchArg.lessThan("date", new java.sql.Date(format.parse(endDay).getTime())),
					MatchArg.equals("userid", uid));
			if (list != null && list.length > 0) {
				double sum = 0;
				for (PersonalBill bill : list) {
					if (bill.getAmount() > 0)
						sum += bill.getAmount();
				}
				System.out.println(sum+" "+startDay+" "+endDay+" "+month);

				return Math.abs(sum);
				

			} else
				return 0;
		} catch (RollbackException e) {
			return 0;
		} catch (ParseException e) {
			return 0;
		}
	}

}
